import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;
import java.io.FileWriter;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * Generate SQL Insert Statements for GuangDian User Data
 * This tool reads CSV file and generates SQL file for manual database import
 */
public class GenerateSqlInsertEn {
    
    public static void main(String[] args) {
        System.out.println("Starting to generate SQL insert statements...");
        
        // CSV file path
        String csvFilePath = "C:/Users/Administrator/RuoYi-Vue/guangdian-data-analysis/processed_data/guangdian_user_wide_table.csv";
        // SQL file output path
        String sqlFilePath = "C:/Users/Administrator/RuoYi-Vue/guangdian_insert.sql";
        
        // Check if CSV file exists
        File csvFile = new File(csvFilePath);
        if (!csvFile.exists()) {
            System.err.println("Error: CSV file not found: " + csvFilePath);
            return;
        }
        
        System.out.println("Found CSV file: " + csvFilePath);
        System.out.println("File size: " + (csvFile.length() / 1024) + "KB");
        
        try (BufferedReader reader = new BufferedReader(new FileReader(csvFilePath));
             FileWriter writer = new FileWriter(sqlFilePath)) {
            
            // Write SQL comments and start statements
            writer.write("-- GuangDian User Data Insert SQL\n");
            writer.write("-- Generated at: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) + "\n");
            writer.write("-- Import limit: 2000 records\n\n");
            writer.write("SET FOREIGN_KEY_CHECKS = 0;\n");
            writer.write("SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n\n");
            
            String line;
            boolean isFirstLine = true; // Skip header line
            int count = 0;
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            
            while ((line = reader.readLine()) != null) {
                if (isFirstLine) {
                    isFirstLine = false;
                    continue;
                }
                
                // Limit to 2000 records
                if (count >= 2000) {
                    break;
                }
                
                String[] fields = line.split(",", -1);
                if (fields.length >= 20) { // Ensure enough fields
                    try {
                        // Generate INSERT statement
                        String insertSql = generateInsertStatement(fields, formatter, dateFormatter);
                        writer.write(insertSql);
                        writer.write("\n");
                        count++;
                        
                        // Show progress every 100 records
                        if (count % 100 == 0) {
                            System.out.println("Generated " + count + " SQL statements");
                        }
                    } catch (Exception e) {
                        System.err.println("Error processing line " + (count + 1) + ": " + e.getMessage());
                        // Skip current line
                    }
                }
            }
            
            // Write end statements
            writer.write("\nSET FOREIGN_KEY_CHECKS = 1;\n");
            writer.write("SET SQL_MODE = DEFAULT;\n");
            
            System.out.println("\nSQL file generation completed!");
            System.out.println("Generated SQL file path: " + sqlFilePath);
            System.out.println("Successfully generated " + count + " insert statements");
            System.out.println("\nInstructions:");
            System.out.println("1. Open MySQL client tool (MySQL Workbench, Navicat, etc.)");
            System.out.println("2. Connect to ry-vue database");
            System.out.println("3. Execute the generated SQL file: source " + sqlFilePath);
            System.out.println("4. After import, refresh dashboard page to view statistics");
            
        } catch (Exception e) {
            System.err.println("Error generating SQL file: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * Generate single INSERT statement
     */
    private static String generateInsertStatement(String[] fields, DateTimeFormatter formatter, DateTimeFormatter dateFormatter) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO guang_dian_user (terminal_no, phone_no, sm_name, run_name, sm_code, owner_name, owner_code, ");
        sql.append("run_time, address, estate_name, open_time, force_flag, is_heavy_user, tv_consumption_level, ");
        sql.append("broadband_consumption_level, is_loyal_user, service_brand, tv_penetration, broadband_penetration, user_type, ");
        sql.append("create_by, create_time) VALUES (");
        
        // Add field values, escape single quotes
        sql.append("'").append(escapeSql(fields[0])).append("', "); // terminal_no
        sql.append("'").append(escapeSql(fields[1])).append("', "); // phone_no
        sql.append("'").append(escapeSql(fields[2])).append("', "); // sm_name
        sql.append("'").append(escapeSql(fields[3])).append("', "); // run_name
        sql.append("'").append(escapeSql(fields[4])).append("', "); // sm_code
        sql.append("'").append(escapeSql(fields[5])).append("', "); // owner_name
        sql.append("'").append(escapeSql(fields[6])).append("', "); // owner_code
        
        // Handle run_time
        if (fields.length > 7 && fields[7] != null && !fields[7].trim().isEmpty()) {
            try {
                LocalDateTime runTime = LocalDateTime.parse(fields[7], formatter);
                sql.append("'").append(runTime.format(formatter)).append("', ");
            } catch (Exception e) {
                sql.append("'").append(LocalDateTime.now().format(formatter)).append("', ");
            }
        } else {
            sql.append("'").append(LocalDateTime.now().format(formatter)).append("', ");
        }
        
        sql.append("'").append(fields.length > 8 ? escapeSql(fields[8]) : "").append("', "); // address
        sql.append("'").append(fields.length > 9 ? escapeSql(fields[9]) : "").append("', "); // estate_name
        
        // Handle open_time
        if (fields.length > 10 && fields[10] != null && !fields[10].trim().isEmpty()) {
            try {
                // First parse to LocalDate, then convert to LocalDateTime
                LocalDateTime openTime = java.time.LocalDate.parse(fields[10], dateFormatter).atStartOfDay();
                sql.append("'").append(openTime.format(formatter)).append("', ");
            } catch (Exception e) {
                sql.append("'").append(LocalDateTime.now().format(formatter)).append("', ");
            }
        } else {
            sql.append("'").append(LocalDateTime.now().format(formatter)).append("', ");
        }
        
        // Add tag fields
        sql.append("'").append(fields.length > 11 ? escapeSql(fields[11]) : "").append("', "); // force_flag
        sql.append("'").append(fields.length > 12 ? escapeSql(fields[12]) : "").append("', "); // is_heavy_user
        sql.append("'").append(fields.length > 13 ? escapeSql(fields[13]) : "").append("', "); // tv_consumption_level
        sql.append("'").append(fields.length > 14 ? escapeSql(fields[14]) : "").append("', "); // broadband_consumption_level
        sql.append("'").append(fields.length > 15 ? escapeSql(fields[15]) : "").append("', "); // is_loyal_user
        sql.append("'").append(fields.length > 16 ? escapeSql(fields[16]) : "").append("', "); // service_brand
        sql.append("'").append(fields.length > 17 ? escapeSql(fields[17]) : "").append("', "); // tv_penetration
        sql.append("'").append(fields.length > 18 ? escapeSql(fields[18]) : "").append("', "); // broadband_penetration
        sql.append("'").append(fields.length > 19 ? escapeSql(fields[19]) : "").append("', "); // user_type
        
        // Add creation info
        sql.append("'system', ");
        sql.append("'").append(LocalDateTime.now().format(formatter)).append("');");
        
        return sql.toString();
    }
    
    /**
     * Escape special characters in SQL string
     */
    private static String escapeSql(String str) {
        if (str == null) {
            return "";
        }
        return str.replace("'", "''");
    }
}